{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians- Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 60)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "band = pd.read_sql_table('band', engine)\n",
    "composer = pd.read_sql_table('composer', engine)\n",
    "composition = pd.read_sql_table('composition', engine)\n",
    "concert = pd.read_sql_table('concert', engine)\n",
    "has_composed = pd.read_sql_table('has_composed', engine)\n",
    "musician = pd.read_sql_table('musician', engine)\n",
    "performance = pd.read_sql_table('performance', engine)\n",
    "performer = pd.read_sql_table('performer', engine)\n",
    "place = pd.read_sql_table('place', engine)\n",
    "plays_in = pd.read_sql_table('plays_in', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "**List the names, dates of birth and the instrument played of living musicians who play a instrument which Theo also plays.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "      <th>born</th>\n",
       "      <th>instrument</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Harry Forte</td>\n",
       "      <td>1951-02-28</td>\n",
       "      <td>violin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Forte</td>\n",
       "      <td>1951-02-28</td>\n",
       "      <td>drums</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James First</td>\n",
       "      <td>1965-06-10</td>\n",
       "      <td>violin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jeff Dawn</td>\n",
       "      <td>1945-12-12</td>\n",
       "      <td>violin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>1950-03-03</td>\n",
       "      <td>violin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        m_name       born instrument\n",
       "2  Harry Forte 1951-02-28     violin\n",
       "3  Harry Forte 1951-02-28      drums\n",
       "1  James First 1965-06-10     violin\n",
       "4    Jeff Dawn 1945-12-12     violin\n",
       "0   John Smith 1950-03-03     violin"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inst = (musician.loc[musician['m_name'].str.contains('^Theo')]\n",
    "        .merge(performer, left_on='m_no', right_on='perf_is')\n",
    "        ['instrument'])\n",
    "(musician.loc[(musician['died'].isnull()) & \n",
    "              (~musician['m_name'].str.contains('^Theo'))]\n",
    " .merge(performer.loc[performer['instrument'].isin(inst)],\n",
    "        left_on='m_no', right_on='perf_is')\n",
    " [['m_name', 'born', 'instrument']].sort_values('m_name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "**List the name and the number of players for the band whose number of players is greater than the average number of players in each band.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>band_name</th>\n",
       "      <th>n_mbr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AASO</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oh well</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ROP</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  band_name  n_mbr\n",
       "0      AASO      7\n",
       "2   Oh well      5\n",
       "3       ROP      7"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (band.merge(plays_in, left_on='band_no', right_on='band_id')\n",
    "     .merge(performer, left_on='player', right_on='perf_no')\n",
    "     [['band_name', 'perf_is']].drop_duplicates())\n",
    "a = (t.groupby('band_name').agg(n_mbr=pd.NamedAgg(column='perf_is', aggfunc='count'))\n",
    "     .reset_index())\n",
    "a.loc[a['n_mbr']>(t.shape[0]/band.shape[0])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "**List the names of musicians who both conduct and compose and live in Britain.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>m_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fred Bloggs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jeff Dawn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Phil Hot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rose Spring</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tony Smythe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        m_name\n",
       "0  Fred Bloggs\n",
       "3    Jeff Dawn\n",
       "2     Phil Hot\n",
       "1  Rose Spring\n",
       "6  Tony Smythe"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(musician.merge(composer, left_on='m_no', right_on='comp_is')\n",
    " .merge(place.loc[place['place_country'].isin(['England', 'Scotland'])], \n",
    "        left_on='living_in', right_on='place_no')\n",
    " .merge(performance, left_on='m_no', right_on='conducted_by')\n",
    " [['m_name']].drop_duplicates().sort_values('m_name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "**Show the least commonly played instrument and the number of musicians who play it.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>instrument</th>\n",
       "      <th>n_mus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>clarinet</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  instrument  n_mus\n",
       "3   clarinet      1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(performer.merge(plays_in, left_on='perf_no', right_on='player')\n",
    " .merge(performance, left_on='band_id', right_on='gave')\n",
    " .groupby('instrument').agg(n_mus=pd.NamedAgg(column='perf_no', aggfunc='count'))\n",
    " .reset_index().sort_values('n_mus').iloc[:1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "**List the bands that have played music composed by Sue Little; Give the titles of the composition in each case.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>band_name</th>\n",
       "      <th>c_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BBSO</td>\n",
       "      <td>Slow Song</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BBSO</td>\n",
       "      <td>Slow Symphony Blowing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Somebody Loves this</td>\n",
       "      <td>Slow Symphony Blowing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Swinging strings</td>\n",
       "      <td>Slow Song</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The left Overs</td>\n",
       "      <td>Slow Song</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             band_name                c_title\n",
       "2                 BBSO              Slow Song\n",
       "3                 BBSO  Slow Symphony Blowing\n",
       "4  Somebody Loves this  Slow Symphony Blowing\n",
       "1     Swinging strings              Slow Song\n",
       "0       The left Overs              Slow Song"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (composition.merge(has_composed, left_on='c_no', right_on='cmpn_no')\n",
    "     .merge(composer, left_on='cmpr_no', right_on='comp_no')\n",
    "     .merge(musician.loc[musician['m_name']=='Sue Little'],\n",
    "            left_on='comp_is', right_on='m_no')\n",
    "     [['c_no', 'c_title']])\n",
    "(t.merge(performance, left_on='c_no', right_on='performed')\n",
    " .merge(band, left_on='gave', right_on='band_no')\n",
    " [['band_name', 'c_title']].sort_values('band_name'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
